package PersistanceLayer;
import BusinessLayer.*;

import PersistanceLayer.DAO;

import PersistanceLayer.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.util.ArrayList;

import jdbc.ConnectionToOracle;

public class EtudiantDAO extends DAO<Etudiant> {

	private static final EtudiantDAO instance = new EtudiantDAO();

	/* Methode permettant de recuperer l'objet unique de type AnneeDAO
	 */
	public final static EtudiantDAO getInstance() {
		return instance;
	}

	@Override
	public boolean create(Etudiant obj) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	@Override
	public boolean update(Etudiant obj) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	@Override
	public boolean update(Etudiant ancien, Etudiant nouveau) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	@Override
	public Etudiant find(Etudiant obj) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	@Override
	public boolean delete(Etudiant obj) {
		throw new UnsupportedOperationException("Not supported yet.");
	}






	public ArrayList<Etudiant> getListe() {


		ArrayList<Etudiant> ensEtud = new ArrayList<Etudiant>();
		String nom = null;
		String prenom = null;
		String sexe= null;
		String mail= null; 
		String numEtud= null;
		String numIne= null; 
		String pts= null;
		String com= null;
		String statut= null;
		String scoreToeic= null;
		String id= null;

		try {

			java.sql.Statement state =this.connect.createStatement();

			ResultSet res = this.connect.createStatement().executeQuery("SELECT * FROM ETUDIANT ");



			ResultSet res2 = null;
			ResultSet res3 = null;

			/** jointure avec la table personne 
			 * 
			 */
			
			while(res.next()){	

				
				res2 = this.connect.createStatement().executeQuery("SELECT * FROM  PERSONNE WHERE ID = '" + res.getObject(1)+"'");


				res3 = this.connect.createStatement().executeQuery("select libellestatut " +
						"from personne p, avoir_statut a, statut s " +
						"where p.id ='" + res.getObject(1)+"'");



					
				if(res.getObject(1) != null)
					id = res.getObject(1).toString();
				else
					id = "";
					if(res.getObject(6) != null)
						numEtud = res.getObject(6).toString();
					else
						numEtud = "";

					if(res.getObject(5) != null)
						numIne = res.getObject(5).toString();
					else
						numIne = "";

					if(res.getObject(7) != null)
						scoreToeic = res.getObject(7).toString();
					else
						scoreToeic= "";

					if(res.getObject(8) != null)
						pts =res.getObject(8).toString();
					else
						pts= "";
					if(res.getObject(9) != null)
						com = res.getObject(9).toString();
					else
						com = "";



					/**
					 * on récupére les attributs de Personne
					 */
					while(res2.next()){

						if(res2.getObject(2) != null)
							nom =res2.getObject(2).toString();
						else
							nom= "";
						if(res2.getObject(3) != null)
							prenom = res2.getObject(3).toString();
						else
							prenom= "";
						if(res2.getObject(4) != null)
							sexe =res2.getObject(4).toString();
						else
							sexe= "";
						if(res2.getObject(5) != null)
							mail = res2.getObject(5).toString();
						else
							mail= "";




							ensEtud.add(new Etudiant(nom,prenom,sexe,mail,numEtud,numIne,statut ,scoreToeic ,pts, com,id));


						
					}
				}
			
			state.close();
			res.close();
			res2.close();
			res3.close();

			return ensEtud;

		}
		catch (SQLException e) {
			// 

			e.printStackTrace();
			return null;
		}


	}



	public ArrayList<Etudiant> getListe( String nomAnnee) {


		ArrayList<Etudiant> ensEtud = new ArrayList<Etudiant>();
		String nom = null;
		String prenom = null;
		String sexe= null;
		String mail= null; 
		String numEtud= null;
		String numIne= null; 
		String pts= null;
		String com= null;
		String statut= null;
		String scoreToeic= null;
		String id= null;

		try {

			java.sql.Statement state =this.connect.createStatement();

			ResultSet res = this.connect.createStatement().executeQuery("SELECT * FROM ETUDIANT ");
			
			ResultSet res2 = null;
			ResultSet res3 = null;

			/** jointure avec la table personne 
			 * 
			 */
			
			while(res.next()){	

				res2 = this.connect.createStatement().executeQuery("SELECT * FROM  PERSONNE WHERE ID ='" + res.getObject(1)+"'");
				

				res3 = this.connect.createStatement().executeQuery("select libellestatut " +
						"from personne p, avoir_statut a, statut s " +
						"where p.id ='" + res.getObject(1)+"'");
				

				if(res.getObject(1) != null)
					id = res.getObject(1).toString();
				else
					id = " ";

					if(res.getObject(6) != null)
						numEtud = res.getObject(6).toString();
					else
						numEtud = " ";

					if(res.getObject(5) != null)
						numIne = res.getObject(5).toString();
					else
						numIne = "";

					if(res.getObject(7) != null)
						scoreToeic = res.getObject(7).toString();
					else
						scoreToeic= null;

					if(res.getObject(8) != null)
						pts =res.getObject(8).toString();
					else
						pts= null;
					if(res.getObject(9) != null)
						com = res.getObject(9).toString();
					else
						com = null;


					/**
					 * on recupere les attributs de Personne
					 */
					while(res2.next()){

						if(res2.getObject(2) != null)
							nom =res2.getObject(2).toString();
						else
							nom= null;
						if(res2.getObject(3) != null)
							prenom = res2.getObject(3).toString();
						else
							prenom= null;
						if(res2.getObject(4) != null)
							sexe =res2.getObject(4).toString();
						else
							sexe= null;
						if(res2.getObject(5) != null)
							mail = res2.getObject(5).toString();
						else
							mail= null;


							/*System.out.println(nom +" | " + prenom +" | "  + sexe +" | "   + mail +" | " +
									numEtud +" | "  + numIne +" | "  + statut +" | "  + scoreToeic +" | " 
									+pts +" | "  + com +" | "  + id +" | " );*/


							ensEtud.add(new Etudiant(nom,prenom,sexe,mail,numEtud,numIne,statut ,scoreToeic ,pts, com,id));

						
					}
					res2.close();
				}
			
			state.close();
			res.close();
			if(res3 != null)
				res3.close();
			return ensEtud;

		}
		catch (SQLException e) {
			// 

			e.printStackTrace();
			return null;
		}


	}


	@Override
	public String getNom(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean update(String a, String b, String c, String d) {
		// TODO Auto-generated method stub
		return false;
	}

   /*public static void main(String[] args){
		
    	Connection connexion;
    	EtudiantDAO etud= new EtudiantDAO();
    	ArrayList<Etudiant> listEtud = new ArrayList<Etudiant>();
    	
    	
    	connexion = ConnectionToOracle.getInstance();
    	
    	listEtud=etud.getListe("4ig");
    	   		
    	
    	}*/



}